package com.hzyb.shopEvaluation.dao;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.hzyb.shopEvaluation.model.ShopEvaluation;
@Repository
@Transactional
public interface ShopEvaluationDao extends CrudRepository<ShopEvaluation, String>{

	@Query(nativeQuery=true,value="select count(1) as count from shop_evaluation where commodity_sid in "
			+ "(select commodity_sid from shop_commodity where merchant_sid= "
			+ "(select merchant_sid from shop_commodity where commodity_sid=?1))")
	public String getTotalShopEvaluationByCommoditySid(String commoditySid);

	@Query(nativeQuery=true,value="select sum(ifnull(score,0)) from shop_evaluation where commodity_sid in"
			+ "(select commodity_sid from shop_commodity where merchant_sid= "
			+ "(select merchant_sid from shop_commodity where commodity_sid=?1))")
	public String getReputationByCommoditySid(String commoditySid);

	@Query(nativeQuery=true,value="select * from shop_evaluation where commodity_sid in"
			+ "(select commodity_sid from shop_commodity where merchant_sid= "
			+ "(select merchant_sid from shop_commodity where commodity_sid=?1))")
	public List<ShopEvaluation> findByCommoditySid(String commoditySid);
	
	
	public List<ShopEvaluation> findByCommoditySidAndGrade(String commoditySid,String grade);


}
